package com.fuful.tools;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;
    private static String url = "jdbc:mysql://localhost:3306/skdb";


    //创建连接
    private static Connection getConn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url,"root","qaz123");
        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }

    //关闭
    private static void closeAll(){
        try{
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //查询
    public static List<Map<String,Object>> query(String sql){
        try{
            connection = getConn();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            List<Map<String,Object>> data = new ArrayList<>();
            while (resultSet.next()){
                //获取各个列
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                HashMap<String,Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount;i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    row.put(columnName,resultSet.getObject(i));
                }
                data.add(row);
            }
            return data;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    //非查询
    public static int executeUpdata(String sql){
        try {
            connection = getConn();
            statement = connection.createStatement();
            int flag = statement.executeUpdate(sql);
            return flag;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return 0;
    }

    //批量非查询[
    public static int[] executeUpdataBatch(List<String> sqls){
        try{
            connection = getConn();
            //添加事务操作，防止自动提交
            connection.setAutoCommit(false);
            statement = connection.createStatement();
            //添加Batch
            for (int i = 0; i < sqls.size(); i++) {
                statement.addBatch(sqls.get(i));
            }
            //执行Batch
            int[] flags = statement.executeBatch();
            //判断是否全部执行成功
            for (int i = 0; i < flags.length; i++) {
                if(flags[i] <= 0){
                    connection.rollback();
                    return null;
                }
            }
            connection.commit();
            return flags;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
}
